'''
操作数据库
'''
import pymysql


def connect(db_info):
    '''
    连接数据库
    :param db_info: 存放数据库信息的字典
    :return: 连接对象
    db = {"host":"192.168.150.96","port":"3306","pwd":"123456","dbname":"apple"}

    '''
    try:
        conn = pymysql.connect(user=db_info["user"],
                               password=db_info["pwd"],
                               host=db_info["host"],
                               database=db_info["dbname"],
                               port=db_info["port"],
                               charset="utf8")
        print("连接数据库成功")
        return conn


    except Exception as e:
        print(f"连接数据库异常，异常信息为：{e}")


# 断开连接
def disconnect(conn):
    try:
        conn.close()
        print("断开数据库成功")
    except Exception as e:
        print(f"连接数据库失败，异常为{e}")


# 执行SQL语句的方法
def execute(conn, sql):
    '''
    执行SQL语句
    :param conn:  connect方法返回的连接对象
    :param sql:  要执行的SQL语句
    :return:
    '''
    try:
        c = conn.cursor()  # 获取游标
        c.execute(sql)
        r = c.fetchall() #获取结果
        conn.commit()

        print(f"执行SQL语句成功：{sql}")
        return r
    except Exception as e:
        print(f"执行SQL语句失败：{sql} , 异常为{e}")


# 删除用户的方法
def delete_user(db, phone):
    c = connect(db)
    execute(c, f"DELETE FROM member WHERE mobilephone='{phone}'")
    disconnect(c)


# 查询用户的方法
def find_user(db, phone):
    c = connect(db)
    d = execute(c, f"SELECT * FROM member WHERE mobilephone='{phone}'")
    return d

def get_id(db,phone):
    c = connect(db)
    r = execute(c, f"SELECT id FROM member WHERE mobilephone='{phone}'")
    # print(r)
    disconnect(c)
    try:
      return  r[0][0]
    except Exception as e:
        print(f"未查询到，异常为{e}")

def delete_loan(db,id):
    c=connect(db)
    r = execute(c,f"DELETE FROM loan WHERE memberId='{id}'")
    disconnect(c)


# 测试代码
if __name__ == '__main__':
    db = {"host": "192.168.150.96", "user": "root", "port": 3306, "pwd": "123456", "dbname": "apple"}
    id= get_id(db,"17691230367")
    print(id)